# -*- coding: utf-8 -*
import re
import os
import time
import random
import pymysql
import threading
import subprocess
from ..common import Tuner, TUNING_PARAMETER
from utils.enums import SystemType
from tqdm import trange, tqdm
from collection.disk.iostat import DiskMetricsCollector
from collection.memory.free import MemMetricCollector

HOST = 'localhost'
USER = 'root'
PASSWORD = 'Wlh123456@'
DATABASE = 'test'
TIME_THRESHOLD = 20
DB_CONFIG_PATH = "/etc/my.cnf"
INNODB_BUFFER_PROPORTION = 0.6
BENCHMARK_NUM = 100

MYSQL_CONFIG_PARAM = {
    "character_set_server": "utf8",
    "bind-address": "0.0.0.0",
    "innodb_log_buffer_size": "64M",
    "innodb_log_file_size": "256M",
    "innodb_log_files_in_group": "2",
    "innodb_file_per_table": "1",
    "innodb_status_file": "OFF",
    "max_connections": "600",
    "max_connect_errors": "1000",
    "max_user_connections": "400",
    "max_heap_table_size": "100M",
    "tmp_table_size": "100M",
    "sort_buffer_size": "2M",
    "join_buffer_size": "2M",
    "read_buffer_size": "2M",
    "read_rnd_buffer_size": "2M",
    "key_buffer_size": "8M",
    "interactive_timeout": "20",
    "wait_timeout": "20",
    "innodb_buffer_pool_dump_at_shutdown": "1",
    "innodb_buffer_pool_load_at_startup": "1",
}


class MysqlTuner(Tuner):
    """针对于Mysql进行调优"""
    def __init__(self):
        super().__init__()
        # self.conn = None
        self.stop_event = threading.Event()
        self.mem_collector = MemMetricCollector(False)
        self.disk_collector = DiskMetricsCollector(False)
        self.last_mem_metrics = None
        self.last_disk_metrics = None
        self._init_benchmark()
        self.paras_index = {
            "vm": 0,
            "kernel": 0,
            "fs": 0,
            "net": 0
        }

    def plain_system_layer_tune(self, systype: SystemType, num: int) -> None:
        """以朴素算法调整linux系统的内核参数"""
        if not isinstance(systype, SystemType):
            raise TypeError("Type of 'systype' must be 'SystemType'!")

        now = time.time()
        # if now - self.timer < TIME_THRESHOLD:
        #     return

        params = TUNING_PARAMETER[systype.value]
        index = self.paras_index[systype.value]
        upper_limit = self.upper_limit[systype.value]
        while num != 0:
            param = params[index]
            complete_param = ".".join([systype.value, param])
            now = self.query(complete_param)
            choices = [-1, 1]
            symbol = random.choice(choices)
            # 随机加减
            now = now + symbol * self.delta[systype.value][param]
            if now < self.limit[systype.value][param][0]:
                now = self.limit[systype.value][param][0]
            if now > self.limit[systype.value][param][1]:
                now = self.limit[systype.value][param][1]

            ok = self.modify(complete_param, now)
            if not ok:
                self.logger.error("Modify system parameter failed.")
                exit(1)

            num -= 1
            index = (index + 1) % upper_limit
            self.log(False, complete_param, symbol * self.delta[systype.value][param])

        self.paras_index[systype.value] = index
        self.benchmark(num, False)
        now = time.time()
        self.timer = now

    def plain_software_layer_tune(self):
        """以朴素算法调整mysql配置文件的参数"""
        print("调整mysql应用层面参数：")
        with tqdm(total=100) as pbar:
            # 获取原有配置文件中的配置项
            cmd1 = "cat {}".format(DB_CONFIG_PATH)
            cmd2 = "grep -v ^#"
            process1 = subprocess.Popen(cmd1.split(), stdout=subprocess.PIPE, stderr=subprocess.PIPE)
            process2 = subprocess.Popen(cmd2.split(), stdin=process1.stdout, stdout=subprocess.PIPE,
                                        stderr=subprocess.PIPE)
            output = process2.communicate()[0]
            res_conf = str(output, encoding="utf-8")
            pbar.update(15)

            # 将优化后的配置项写入
            for key, val in MYSQL_CONFIG_PARAM.items():
                metric = "=".join([key, val])
                metric += "\n"
                res_conf += metric
            pbar.update(20)

            # 获取物理内存为多少，根据物理内存设置innodb_buffer_pool_size
            cmd = "cat /proc/meminfo"
            process = subprocess.run(cmd.split(), stdout=subprocess.PIPE, stderr=subprocess.PIPE)
            out = str(process.stdout, encoding="utf-8")
            pos = out.find("kB")
            pat = re.compile(r"\d+")
            tot_mem_size = int(pat.findall(out[:pos])[0])
            tot_mem_size_mb = tot_mem_size / 1024
            innodb_buffer_pool_size = int(tot_mem_size_mb * INNODB_BUFFER_PROPORTION)
            metric = "innodb_buffer_pool_size={}\n".format(innodb_buffer_pool_size)
            res_conf += metric
            pbar.update(10)

            # 获取所有CPU核心数量，以此来设置innodb_read_io_threads和innodb_write_io_threads
            sh = os.path.join(os.path.abspath("."), "scripts", "cpu_num.sh")
            out = subprocess.run(["bash", sh], stdout=subprocess.PIPE, stderr=subprocess.PIPE)
            num = int(out.stdout)

            # 查看当前数据库场景是写操作更为频繁还是读操作更为频繁
            process = subprocess.Popen(["mysql", "-u", USER, "-p{}".format(PASSWORD)], stdin=subprocess.PIPE,
                                       stdout=subprocess.PIPE, stderr=subprocess.PIPE)
            out = process.communicate()[-1]
            out_str = str(out, encoding="utf-8")
            pos = out_str.find("Access denied")
            if pos != -1:
                raise RuntimeError("MySQL Access denied.")

            process = subprocess.Popen(["mysql", "-u", USER, "-p{}".format(PASSWORD)], stdin=subprocess.PIPE,
                                       stdout=subprocess.PIPE, stderr=subprocess.PIPE)
            process.stdin.write(b"SHOW STATUS LIKE 'Com_select';")
            out = process.communicate()[0]
            out_str = str(out, encoding="utf-8")
            read_num = int(pat.findall(out_str)[0])

            # 重庆开启mysql进程
            process = subprocess.Popen(["mysql", "-u", USER, "-p{}".format(PASSWORD)], stdin=subprocess.PIPE,
                                       stdout=subprocess.PIPE, stderr=subprocess.PIPE)
            process.stdin.write(b"SHOW STATUS LIKE 'Com_insert';")
            out = process.communicate()[0]
            out_str = str(out, encoding="utf-8")
            write_num = int(pat.findall(out_str)[0])

            read_proportion = read_num / (read_num + write_num)
            cpu_read = round(read_proportion * num)  # 分给读操作的cpu核数量
            cpu_write = num - cpu_read
            if cpu_read == 0:
                cpu_read += 1
                cpu_write -= 1
            elif cpu_write == 0:
                cpu_write += 1
                cpu_read -= 1

            metric = "innodb_read_io_threads={}\n".format(cpu_read)
            res_conf += metric
            metric = "innodb_write_io_threads={}\n".format(cpu_write)
            res_conf += metric
            pbar.update(25)

            # 将优化过后的配置写入到mysql配置文件中
            with open(DB_CONFIG_PATH, "w", encoding="utf-8") as f:
                f.write(res_conf)

            # 重启mysqld守护进程
            subprocess.run(["service", "mysqld", "restart"], stdout=subprocess.PIPE, stderr=subprocess.PIPE)
            pbar.update(30)

    def _init_benchmark(self) -> None:
        """调优前首次进行一下基础测试"""
        self.stop_event = threading.Event()

        threads = []
        for _ in range(20):
            conn = pymysql.connect(host=HOST,
                                   user=USER,
                                   password=PASSWORD,
                                   database=DATABASE,
                                   cursorclass=pymysql.cursors.DictCursor)
            t = threading.Thread(target=self.database_operations, args=(self.stop_event, conn))
            t.start()
            threads.append(t)

        self.logger.info("正在进行MySQL场景优化前的首次性能测试：")
        for i in trange(BENCHMARK_NUM):
            if i == 0.8 * BENCHMARK_NUM:
                self.last_mem_metrics = self.mem_collector.collect()
                self.last_disk_metrics = self.disk_collector.collect()
            time.sleep(0.01)

        self.stop_event.set()
        for t in threads:
            t.join()
        return None

    def benchmark(self, num: int or None, is_qlearning: bool) -> None or float:
        """
        mysql主要占用内存和磁盘，因此仅收集内存和磁盘的数据进行测试
        :param num: 表示若需要回滚，则回滚多少次
        :param is_qlearning: 是否是在q-learning算法中使用性能测试
        :return: None or float
        """
        self.stop_event = threading.Event()
        last_mem_metrics = self.last_mem_metrics
        last_disk_metrics = self.last_disk_metrics

        threads = []
        for _ in range(20):
            conn = pymysql.connect(host=HOST,
                                   user=USER,
                                   password=PASSWORD,
                                   database=DATABASE,
                                   cursorclass=pymysql.cursors.DictCursor)
            t = threading.Thread(target=self.database_operations, args=(self.stop_event, conn))
            t.start()
            threads.append(t)

        if not is_qlearning:
            self.logger.info("正在进行MySQL场景调优后的性能测试：")
        disk_up = None
        mem_up = None

        if not is_qlearning:
            for i in trange(BENCHMARK_NUM):
                if i == 0.8 * BENCHMARK_NUM:
                    now_mem_metrics = self.mem_collector.collect()
                    now_disk_metrics = self.disk_collector.collect()
                    disk_up = self.evaluate_disk(last_disk_metrics, now_disk_metrics)
                    mem_up = self.evaluate_mem(last_mem_metrics, now_mem_metrics)
                    # 若性能有所下降
                    if disk_up < -self.blunder_threshold or mem_up < -self.blunder_threshold:
                        self.rollback(num)
                time.sleep(0.01)
        else:
            for i in range(BENCHMARK_NUM):
                if i == 0.8 * BENCHMARK_NUM:
                    now_mem_metrics = self.mem_collector.collect()
                    now_disk_metrics = self.disk_collector.collect()
                    disk_up = self.evaluate_disk(last_disk_metrics, now_disk_metrics)
                    mem_up = self.evaluate_mem(last_mem_metrics, now_mem_metrics)
                    return (disk_up + mem_up) / 2
                time.sleep(0.01)

        if not is_qlearning:
            if (disk_up + mem_up) / 2 < 0:
                self.logger.info("经调优后，MySQL性能降低{}%.".format((disk_up + mem_up) / 2))
                if disk_up < -self.blunder_threshold or mem_up < -self.blunder_threshold:
                    self.logger.info("回滚本次参数修改操作.")
            else:
                self.logger.info("经调优后，MySQL性能提升{}%.".format((disk_up + mem_up) / 2))

        self.stop_event.set()
        for t in threads:
            t.join()
        return None

    def database_operations(self, stop_event, conn):
        try:
            self.create(conn)
            while not stop_event.is_set():
                self.insert(conn)
                self.read(conn)
                self.delete(conn)
                time.sleep(0.01)
        except Exception as e:
            self.logger.error(e)
        finally:
            conn.close()

    @staticmethod
    def create(conn):
        """创建新表"""
        with conn.cursor() as cursor:
            sql = "CREATE TABLE IF NOT EXISTS test_table (name VARCHAR(15), age INT, note VARCHAR(30))"
            conn.ping(reconnect=True)
            cursor.execute(sql)
        conn.commit()

    @staticmethod
    def insert(conn):
        """插入数据"""
        with conn.cursor() as cursor:
            sql = "INSERT INTO test_table (name, age, note) VALUES ('Alice', 18, 'This is a note')"
            conn.ping(reconnect=True)
            cursor.execute(sql)
        conn.commit()

    @staticmethod
    def read(conn):
        """读取数据"""
        with conn.cursor() as cursor:
            sql = "SELECT * FROM test_table"
            conn.ping(reconnect=True)
            cursor.execute(sql)

    @staticmethod
    def delete(conn):
        """删除数据"""
        with conn.cursor() as cursor:
            sql = "DELETE FROM test_table WHERE age = 18"
            conn.ping(reconnect=True)
            cursor.execute(sql)
        conn.commit()
